package com.monkeyk.sos.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.monkeyk.sos.domain.model.CooperDetails;
import com.monkeyk.sos.domain.model.IdentifyingCode;
import com.monkeyk.sos.service.CommonService;
import com.monkeyk.sos.service.CooperService;
import com.monkeyk.sos.util.DateFormatTool;

@Service("commonService")
public class CommonServiceImpl implements CommonService {
    
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private CooperService cooperService;
	
	
	/*
	 * type0:手机号可以使用
	 * type1:手机号已被使用
	 * type2:手机号验证次数超过上限10
	 */
	@Override
	public String getMessCodeStatus(String phone) {
		boolean phoneStatus = cooperService.checkCooperPhone(phone);
		if(phoneStatus){
			int count = getMessCodeCount(phone);
			if(count <10){
				return "type0";
			}else{
				return "type2";
			}
		}else{
			return "type1";
		}
	}
	/*
	 * 查询手机号当天获得验证码次数
	 */
	private int getMessCodeCount(String phone) {
		final String sql = " select count(0) from identifying_code where  phone = '"+phone+"' and date_format(createdate,'%Y-%m-%d') = "+DateFormatTool.getTodays();
		return this.jdbcTemplate.queryForObject(sql,
				Integer.class);
	}
	
	/*
	 * 保存验证码
	 */
	@Override
	public int saveMessCode( String phone, String email, String code) {
		return saveMessCodeD(phone,null,code);
	}
	
	/*
	 * 保存短信验证码信息identifying_code
	 */
	private int saveMessCodeD(final String phone, final String email, final String code) {
		final String sql = " insert into identifying_code(phone,code,email) values (?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, phone);
				ps.setString(2, code);
				ps.setString(3, email);
				return ps;
			}
		}, keyHolder);
		int codeId = keyHolder.getKey().intValue();
		return codeId;
	}

	/*
	 * 比较验证码正确和失效60秒
	 * type0:验证通过
	 * type1:验证码过期
	 * type2:验证码错误
	 */
	@Override
	public String checkMessCode(String codeId, String code,String password) {
		IdentifyingCode identifyingCode =getMessCode(codeId);
		int mis = DateFormatTool.getMis(identifyingCode.getCreatedate(), new Date());
		if(code.equals(identifyingCode.getCode()) && mis < 60){
			CooperDetails cooper= new CooperDetails();
			cooper.setPhone(identifyingCode.getPhone());
			cooper.setPassword(password);
			cooperService.saveCooper(cooper);
			return "type0";
		}else if(code.equals(identifyingCode.getCode()) && mis >= 60){
			return "type1";
		}else if(!code.equals(identifyingCode.getCode()) && mis < 60){
			return "type2";
		}else {
			return "type2";
		}
	}
	
	/*
	 * 查询验证码信息
	 */
	private IdentifyingCode getMessCode(final String codeId) {
		String sqlApp = " select * from identifying_code where  id = ?";
		java.util.List<IdentifyingCode> codeList = this.jdbcTemplate.query(sqlApp, new Object[] { codeId },
				new RowMapper<IdentifyingCode>() {
					@Override
					public IdentifyingCode mapRow(ResultSet rs, int rowNum) throws SQLException {
						IdentifyingCode codeD = new IdentifyingCode();
						codeD.setPhone(rs.getString("phone"));
						codeD.setEmail(rs.getString("email"));
						codeD.setCode(rs.getString("code"));
						codeD.setCreatedate(rs.getTimestamp("createdate"));
						return codeD;
					}
				});
		IdentifyingCode code = null;
		if(codeList.size() >0){
			code=codeList.get(0);
		}
		return code;
	}

	
}